package com.navi.mould1.service.impl;

import com.navi.bean.in.ConditionBean;
import com.navi.bean.out.halm_gearDistribution_report.HalmGearDistributionReportGridOut;
import com.navi.bean.sql.SqlVo;
import com.navi.mould1.pojo.EfgrPojo;
import com.navi.mould1.pojo.ToolToolgLineFabPojo;
import com.navi.mould1.pojo.WaferSpecPojo;
import com.navi.mould1.service.IHalmGearDistributionReportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.List;
import java.util.stream.Collectors;
import static com.navi.bean.sql.SqlVo.jointInSql;
import static com.navi.core.util.JdbcUtils.mySql_QueryRunner_QueryListBySql;
import static com.navi.mould1.reportUtils.ComputeUtil.getPercent;
import static com.navi.mould1.reportUtils.ComputeUtil.getRate;
import static com.navi.mould1.reportUtils.reportUtil.isStartWithNumber;


@Slf4j
@Service
public class HalmGearDistributionReportServiceImpl implements IHalmGearDistributionReportService {


    public List<HalmGearDistributionReportGridOut> getGridData(ConditionBean conditions) {

        //优化查询速度，把Sql中子查询拿出来单独查
        List<String> tool_id_fk_list = new ArrayList<>();
        if (StringUtils.isNotEmpty(conditions.getToolg_id()) || StringUtils.isNotEmpty(conditions.getTool_type()) || StringUtils.isNotEmpty(conditions.getTool_id())) {
            SqlVo sql = new SqlVo("select tool_id as tool_id_fk from bis_tool where 1=1");
            if (StringUtils.isNotEmpty(conditions.getToolg_id())) sql.appendSql("and root_tool_id in (select tool_id_fk from bis_tool_gr where toolg_id in " + jointInSql(conditions.getToolg_id()) + ")");
            if (StringUtils.isNotEmpty(conditions.getTool_type())) sql.appendSql("and unit_typ in" + jointInSql(conditions.getTool_type()));
            if (StringUtils.isNotEmpty(conditions.getTool_id())) sql.appendSql("and tool_id in" + jointInSql(conditions.getTool_id()));
            List<ToolToolgLineFabPojo> list = mySql_QueryRunner_QueryListBySql(sql, ToolToolgLineFabPojo.class);
            tool_id_fk_list.addAll(list.stream().map(data -> data.getTool_id_fk()).distinct().collect(Collectors.toList()));
        }
        List<String> wafer_spec_list = new ArrayList<>();
        if (StringUtils.isNotEmpty(conditions.getSpec()) || StringUtils.isNotEmpty(conditions.getWafer_spec())) {
            SqlVo sql = new SqlVo("select data_id as wafer_spec from bis_data where data_cate = 'WFSF'");
            if (StringUtils.isNotEmpty(conditions.getSpec())) sql.appendSql("and data_ext in" + jointInSql(conditions.getSpec()));
            if (StringUtils.isNotEmpty(conditions.getWafer_spec())) sql.appendSql("and data_id in" + jointInSql(conditions.getWafer_spec()));
            List<WaferSpecPojo> list = mySql_QueryRunner_QueryListBySql(sql, WaferSpecPojo.class);
            wafer_spec_list.addAll(list.stream().map(data -> data.getWafer_spec()).distinct().collect(Collectors.toList()));
        }

        final SqlVo sqlVo = new SqlVo();
        sqlVo.appendSql("SELECT");
        sqlVo.appendSql("attributes,");
        sqlVo.appendSql("bin,");
        sqlVo.appendSql("total_count as count");
        sqlVo.appendSql("FROM halm_test_data_bin_summary A");
        sqlVo.appendSql("WHERE 1=1");
        //if (StringUtils.isNotEmpty(conditions.getFab_id())) sqlVo.appendSql("AND A.TOOL_ID_FK IN(select tool_id from bis_tool where fab_id_fk in" + jointInSql(conditions.getFab_id()) + ")");
        if (StringUtils.isNotEmpty(conditions.getBay_id())) sqlVo.appendSql("AND A.LINE_ID_FK in" + jointInSql(conditions.getBay_id()));
        //if (StringUtils.isNotEmpty(conditions.getToolg_id())) sqlVo.appendSql("AND A.TOOL_ID_FK in(select tool_id from bis_tool where root_tool_id in (select tool_id_fk from bis_tool_gr where toolg_id in " + jointInSql(conditions.getToolg_id()) + "))");
        //if (StringUtils.isNotEmpty(conditions.getTool_type())) sqlVo.appendSql("AND A.TOOL_ID_FK in(select tool_id from bis_tool where unit_typ in" + jointInSql(conditions.getTool_type()) + ")");
        //if (StringUtils.isNotEmpty(conditions.getTool_id())) sqlVo.appendSql("AND A.TOOL_ID_FK in" + jointInSql(conditions.getTool_id()));
        if (CollectionUtils.isNotEmpty(tool_id_fk_list)) sqlVo.appendSql("AND A.TOOL_ID_FK in" + tool_id_fk_list.stream().distinct().collect(Collectors.joining("','","('","')")));
        //if (StringUtils.isNotEmpty(conditions.getSpec())) sqlVo.appendSql("AND A.WAFER_SPEC in(select data_id from bis_data where data_cate = 'WFSF' and data_ext in" + jointInSql(conditions.getSpec()) + ")");
        //if (StringUtils.isNotEmpty(conditions.getWafer_spec())) sqlVo.appendSql("AND A.WAFER_SPEC in" + jointInSql(conditions.getWafer_spec()));
        if (CollectionUtils.isNotEmpty(wafer_spec_list)) sqlVo.appendSql("AND A.WAFER_SPEC in" + wafer_spec_list.stream().distinct().collect(Collectors.joining("','","('","')")));
        if (StringUtils.isNotEmpty(conditions.getManufacturer())) sqlVo.appendSql("AND A.CUS_ID_FK in" + jointInSql(conditions.getManufacturer()));
        if (StringUtils.isNotEmpty(conditions.getBase_id())) sqlVo.appendSql("AND A.BASE_ID in" + jointInSql(conditions.getBase_id()));
        //if (StringUtils.isNotEmpty(conditions.getAttributes())) sqlVo.appendSql("AND A.ATTRIBUTES in" + jointInSql(conditions.getAttributes()));
        //if (StringUtils.isNotEmpty(conditions.getGear())) sqlVo.appendSql("AND A.GEAR in" + jointInSql(conditions.getGear()));
        if (StringUtils.isNotEmpty(conditions.getLot_id())) sqlVo.appendSql("AND A.LOT_ID in" + jointInSql(conditions.getLot_id()));
        if (StringUtils.isNotEmpty(conditions.getStart_time())) sqlVo.appendSqlAndAddParam("AND CONCAT(INPUT_DATE, ' ', INPUT_TIME, ':00:00') >= ?", conditions.getStart_time()+":00:00");
        if (StringUtils.isNotEmpty(conditions.getEnd_time())) sqlVo.appendSqlAndAddParam("AND CONCAT(INPUT_DATE, ' ', INPUT_TIME, ':00:00') < ?", conditions.getEnd_time()+":00:00");
        //sqlVo.appendSql("GROUP BY gear");

        List<HalmGearDistributionReportGridOut> resultDatas = mySql_QueryRunner_QueryListBySql(sqlVo, HalmGearDistributionReportGridOut.class);

        if (CollectionUtils.isNotEmpty(resultDatas) && StringUtils.isNotEmpty(conditions.getEff_no())) {
            SqlVo sql = new SqlVo("select data_id as attributes, ext_1 as efficiency, ext_2 as bin from bis_data where data_cate = 'EFGR' and data_ext in" + jointInSql(conditions.getEff_no()));
            List<EfgrPojo> efgr_list = mySql_QueryRunner_QueryListBySql(sql, EfgrPojo.class);
            resultDatas.forEach(data -> {
                data.setGear(efgr_list.stream()
                        .filter(efgrPojo -> StringUtils.isNotEmpty(data.getBin()) && data.getBin().equals(efgrPojo.getBin()))
                        .filter(efgrPojo -> StringUtils.isNotEmpty(data.getAttributes()) && data.getAttributes().equals(efgrPojo.getAttributes()))
                        .map(efgrPojo -> efgrPojo.getEfficiency())
                        .findFirst()
                        .orElse("")
                );
            });
        }
        List<HalmGearDistributionReportGridOut> gridOut_list = new ArrayList<>();
        resultDatas.stream().collect(Collectors.groupingBy(data -> data.getGear())).forEach((gear, list) -> {
            if (StringUtils.isEmpty(conditions.getGear()) || Arrays.asList(conditions.getGear().split(",")).contains(gear)) {
                HalmGearDistributionReportGridOut gridOut = new HalmGearDistributionReportGridOut();
                gridOut.setGear(gear);
                gridOut.setCount(list.stream().mapToInt(data -> data.getCount()).sum());
                gridOut_list.add(gridOut);
            }
        });

        List<HalmGearDistributionReportGridOut> final_gridOut_list = new ArrayList<>();
        final_gridOut_list.addAll(gridOut_list.stream().filter(data -> StringUtils.isNotEmpty(data.getGear())).filter(data -> isStartWithNumber(data.getGear())).sorted(Comparator.comparing(HalmGearDistributionReportGridOut::getGear).reversed()).collect(Collectors.toList()));
        final_gridOut_list.addAll(gridOut_list.stream().filter(data -> StringUtils.isNotEmpty(data.getGear())).filter(data -> !isStartWithNumber(data.getGear())).sorted(Comparator.comparing(HalmGearDistributionReportGridOut::getGear)).collect(Collectors.toList()));

        double cumulative_rate = 0;
        double cumulative_bad_rate = 0;
        for (HalmGearDistributionReportGridOut gridOut : final_gridOut_list) {
            gridOut.setTotal_count(final_gridOut_list.stream().mapToInt(data -> data.getCount()).sum());
            gridOut.setOutput_rate(getRate(gridOut.getCount(), gridOut.getTotal_count()));
            gridOut.setOutput_percent(getPercent(gridOut.getOutput_rate()));
            cumulative_rate = cumulative_rate + gridOut.getOutput_rate();
            //增加不良的良率Sum
            if (!isStartWithNumber(gridOut.getGear())) {
                cumulative_bad_rate += gridOut.getOutput_rate();
                gridOut.setCumulative_bad_rate(cumulative_bad_rate);
            }
            gridOut.setCumulative_rate(cumulative_rate);
            gridOut.setCumulative_percent(getPercent(gridOut.getCumulative_rate()));
        }

        return final_gridOut_list;
    }

}
